#!/bin/bash
##--此脚本用于创建测试表空间、用户及数据表，用于检查备份数据是否正常
##--需在root帐号下执行此脚本

ORACLE_SID=ORCL
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/21.0.0/dbhome_1
ORA_INVENTORY=/u01/app/oraInventory
DATA_DIR=/u02/oradata

#删除表空间
#DROP TABLESPACE TBS_QMS_TEMP INCLUDING CONTENTS AND DATAFILES;
#DROP TABLESPACE TBS_QMS INCLUDING CONTENTS AND DATAFILES;

#新建表空间
su - oracle -c "sqlplus / as sysdba <<EOF
set linesize 300;
set pagesize 100;
/*第1步：创建临时表空间  */
create temporary tablespace TBS_QMS_TEMP
tempfile '$DATA_DIR/$ORACLE_SID/QMS_TEMP.dbf'
size 50m
autoextend on
next 50m maxsize 20480m  
extent management local;
/*第2步：创建数据表空间  */
create tablespace TBS_QMS
logging  
datafile '$DATA_DIR/$ORACLE_SID/QMS_01.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local; 
EOF
"

#新建用户
su - oracle -c 'sqlplus / as sysdba <<EOF
/*第3步：创建用户  */
create user C##qms identified by "windows-999"
default tablespace TBS_QMS
temporary tablespace TBS_QMS_TEMP
profile DEFAULT;

/*第4步：授权  */
grant connect to C##qms;
grant dba to C##qms;
grant resource to C##qms;
grant restricted session to C##qms with admin option;
grant unlimited tablespace to C##qms with admin option;

exit;
EOF
'

#测试新建用户能否登陆
su - oracle -c "sqlplus C##qms/windows-999 <<EOF
select user from dual;
exit;
EOF
"

#新建测试表
su - oracle -c "sqlplus C##qms/windows-999 <<EOF
/*测试表*/
create table TST_CRONTB
(
  CRON_ID    INT not null,  --序号
  CRON_TXT   VARCHAR2(200), --文本
  CRON_DATE  DATE           --时间
);
/*序列*/
create sequence SEQ_TC_CRON_ID
minvalue 1
nomaxvalue 
increment by 1 
start with 1
nocache;
/*触发器*/
create trigger TRIG_TC_CRON_ID before insert on TST_CRONTB for each row when (new.CRON_ID is null)
begin
    select SEQ_TC_CRON_ID.nextval into:new.CRON_ID from dual;
end;
/
/*写入一条数据*/
insert into TST_CRONTB(CRON_TXT,CRON_DATE) VALUES ('初始化写入',sysdate);
commit;
exit;
EOF
"
#检查数据写入
su - oracle -c "sqlplus C##qms/windows-999 <<EOF
select * from TST_CRONTB;
exit;
EOF
"
#定时插入测试数据
cat > /home/oracle/scripts/tst-crontb-ins.sh <<EOF
#!/bin/bash
SHELL_DIR="\$( cd "\$( dirname "\$0" )" && pwd )"
PWDPATH=\`pwd\`
txt="写入时间:"\$(date +%Y%m%d%H%M%S)
echo "insert into TST_CRONTB(CRON_TXT,CRON_DATE) VALUES ('\$txt',sysdate);" | tee -a \$SHELL_DIR/insert.log
su - oracle -c "sqlplus C##qms/windows-999 <<EOF
insert into TST_CRONTB(CRON_TXT,CRON_DATE) VALUES ('\$txt',sysdate);
commit;
exit;
EOF
echo "EOF\"">>/home/oracle/scripts/tst-crontb-ins.sh

#其他参考命令
:<<!
su - oracle -c 'rlwrap sqlplus C##qms/windows-999'
alter session set current_schema=C##QMS;
select userenv('language') from dual;
alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";


col CRON_TXT format a30
select CRON_ID,CRON_TXT,to_char(CRON_DATE,'yy-mm-dd hh24:mi:ss') as CRON_DATE from TST_CRONTB order by CRON_ID;

drop trigger TRIG_TC_CRON_ID;
drop sequence SEQ_TC_CRON_ID;
drop table TST_CRONTB;


*/1 * * * * sh /home/oracle/scripts/tst-crontb-ins.sh > /dev/null 2>&1



!